Quarterly Expense Analysis Worksheet

As the chief accountant for College Travel, Inc., you have been asked by the vice president to create a worksheet to analyze the 4th quarter expenses for the company by office and expense category. The office locations and corresponding expenses for the 4th quarter are shown in Table 1.

Instructions: Perform the following tasks.

1. Create the worksheet shown in Figure 1-83 using the data in Table 1.

2. Use the SUM function to determine totals expenses for the four offices, the totals for each expense and the company total. Add column and row headings for the totals, as appropriate.

3. Change the worksheet title to 26-point Arial Black dark red, bold font, and center it across columns A dirough F. Format the worksheet subtitle to 16-point Arial dark red, bold font, and center it across columns A through F.

4. Format the range A3:F9 using the AutoFormat command on the Format menu as follows: (a) apply the autoformat Accounting 1; and (b) with the range A3:F9 still selected, apply the autoformat List 1. If you make a mistake, apply the autoformat None and then apply the auto formats again.

5. Chart the range A3:E8. Draw the 100% Stacked column with a cylindrical shape chart, as shown in Figure 1_83, by clicking the Chart Wizard button on the Standard toolbar. When Excel displays the Chart Wizard dialog box, select Cylinder in the Chart type list, and then select column 1, row 3 in the Chart sub-type list. Use the chart location A10:H21.

6. Save the workbook using the file name, Lab 1-2 College Travel 4th Quarter Expenses.